package com.caipiao.lottery.dbwarpper;

import com.caipiao.lottery.data.UserCookie;
import com.caipiao.lottery.data.UserInfo;
import com.caipiao.lottery.log.TraceLogger;
import com.caipiao.util.db.DBHelper;
import jodd.util.StringUtil;
import org.slf4j.Logger;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

/**
 *
 */
public class P_API_USER {
    public static final String DB_NAME = "danmu";

    public static Logger logger = TraceLogger.DB;

    /**
     * 查询除该用户外在线用户列个数
     *
     * @return
     */
    public static boolean queryOnlineUserCount(String msg_id, String userId, Map<String, Integer> result) {
        boolean flag = false;

        String sql = "";
        Connection conn = null;
        NamedParameterStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.Instance().getConnection(msg_id, DB_NAME, logger); // 连接池获取链接
            if (null == conn) {
                throw new Exception(msg_id + "-获取数据库连接异常,:DBPoolName" + DB_NAME);
            }

            //判断sessionId是否有效，并获取userId
            sql = " select " +
                    " count(1) count " +
                    " from tb_user_info " +
                    " where onlineStatus = 1 " +
                    " and id <> :id ";
            ps = new NamedParameterStatement(conn, sql);
            ps.setString("id", userId);
            rs = ps.executeQuery();
            rs.next();
            result.put("count", rs.getInt("count"));

            flag = true;
        } catch (Exception e) {
            logger.error(msg_id + ":" + DB_NAME + " - " + sql + "执行异常:"
                    + e);
        } finally {
            DBHelper.Instance().closeResource(conn, ps, rs); // 释放资源
            return flag;
        }
    }

    /**
     * 修改用户在线状态
     *
     * @return
     */
    @SuppressWarnings("finally")
    public static boolean updateUserOnlineStatus(String msg_id,
                                                 String id, String onlineStatus) {
        boolean flag = false;

        String sql = "";
        Connection conn = null;
        NamedParameterStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.Instance().getConnection(msg_id, DB_NAME, logger); // 连接池获取链接
            if (null == conn) {
                throw new Exception(msg_id + "-获取数据库连接异常,:DBPoolName" + DB_NAME);
            }
            conn.setAutoCommit(false);

            sql = " UPDATE tb_user_info" +
                    " set onlineStatus = :onlineStatus " +
                    " where id = :id ";
            ps = new NamedParameterStatement(conn, sql);
            ps.setString("id", id);
            ps.setString("onlineStatus", onlineStatus);
            int count = ps.executeUpdate();
            if (count > 0) {
                flag = true;// 记录数据库输出参数
                logger.info(msg_id + " - 修改用户在线状态[id:" + id + "]执行状态为成功,完成");
                conn.commit();
            }

            flag = true;
        } catch (Exception e) {
            conn.rollback();
            logger.error(msg_id + ":" + DB_NAME + " - " + sql + "执行异常:"
                    + e);
        } finally {
            DBHelper.Instance().closeResource(conn, ps, rs); // 释放资源
            return flag;
        }
    }

    /**
     * 初始化cookie状态，将userId设置为0
     *
     * @return
     */
    @SuppressWarnings("finally")
    public static boolean initCookieUserId(String msg_id) {
        boolean flag = false;

        String sql = "";
        Connection conn = null;
        NamedParameterStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.Instance().getConnection(msg_id, DB_NAME, logger); // 连接池获取链接
            if (null == conn) {
                throw new Exception(msg_id + "-获取数据库连接异常,:DBPoolName" + DB_NAME);
            }
            conn.setAutoCommit(false);

            //修改cookile为中间状态
            sql = " UPDATE tb_cookie" +
                    " set userId = 0 ";
            ps = new NamedParameterStatement(conn, sql);
            ps.executeUpdate();
            flag = true;
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            logger.error(msg_id + ":" + DB_NAME + " - " + sql + "执行异常:"
                    + e);
        } finally {
            DBHelper.Instance().closeResource(conn, ps, rs); // 释放资源
            return flag;
        }
    }

    /**
     * 初始化用户在线状态
     *
     * @return
     */
    @SuppressWarnings("finally")
    public static boolean initUserOnline(String msg_id) {
        boolean flag = false;

        String sql = "";
        Connection conn = null;
        NamedParameterStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.Instance().getConnection(msg_id, DB_NAME, logger); // 连接池获取链接
            if (null == conn) {
                throw new Exception(msg_id + "-获取数据库连接异常,:DBPoolName" + DB_NAME);
            }
            conn.setAutoCommit(false);

            //修改cookile为中间状态
            sql = " UPDATE tb_user_info" +
                    " set onlineStatus = 0 " +
                    " where onlineStatus <> 0";
            ps = new NamedParameterStatement(conn, sql);
            ps.executeUpdate();
            flag = true;
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            logger.error(msg_id + ":" + DB_NAME + " - " + sql + "执行异常:"
                    + e);
        } finally {
            DBHelper.Instance().closeResource(conn, ps, rs); // 释放资源
            return flag;
        }
    }


    /**
     * 执行完成后，释放cookie
     *
     * @return
     */
    @SuppressWarnings("finally")
    public static boolean freeCookie(String msg_id,
                                     String id) {
        boolean flag = false;

        String sql = "";
        Connection conn = null;
        NamedParameterStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.Instance().getConnection(msg_id, DB_NAME, logger); // 连接池获取链接
            if (null == conn) {
                throw new Exception(msg_id + "-获取数据库连接异常,:DBPoolName" + DB_NAME);
            }
            conn.setAutoCommit(false);

            sql = " UPDATE tb_cookie" +
                    " set userId = 0 " +
                    " where id = :id ";
            ps = new NamedParameterStatement(conn, sql);
            ps.setString("id", id);
            int count = ps.executeUpdate();
            if (count > 0) {
                flag = true;// 记录数据库输出参数
                logger.info(msg_id + " - 执行完成后，释放cookie[id:" + id + "]执行状态为成功,完成");
                conn.commit();
            }

            flag = true;
        } catch (Exception e) {
            conn.rollback();
            logger.error(msg_id + ":" + DB_NAME + " - " + sql + "执行异常:"
                    + e);
        } finally {
            DBHelper.Instance().closeResource(conn, ps, rs); // 释放资源
            return flag;
        }
    }


    /**
     * 发送完信息，修改最后运行时间
     *
     * @return
     */
    @SuppressWarnings("finally")
    public static boolean updateCookieLastRunTime(String msg_id,
                                                  String id, String lastRunTime) {
        boolean flag = false;

        String sql = "";
        Connection conn = null;
        NamedParameterStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.Instance().getConnection(msg_id, DB_NAME, logger); // 连接池获取链接
            if (null == conn) {
                throw new Exception(msg_id + "-获取数据库连接异常,:DBPoolName" + DB_NAME);
            }
            conn.setAutoCommit(false);

            //判断sessionId是否有效，并获取userId
            sql = " UPDATE tb_cookie" +
                    " set lastRunTime = :lastRunTime" +
                    " where id = :id ";
            ps = new NamedParameterStatement(conn, sql);
            ps.setString("lastRunTime", lastRunTime);
            ps.setString("id", id);
            int count = ps.executeUpdate();
            if (count > 0) {
                flag = true;// 记录数据库输出参数
                logger.info(msg_id + " - 发送完信息，修改最后运行时间[id:" + id + "]执行状态为成功,完成");
                conn.commit();
            }

            flag = true;
        } catch (Exception e) {
            conn.rollback();
            logger.error(msg_id + ":" + DB_NAME + " - " + sql + "执行异常:"
                    + e);
        } finally {
            DBHelper.Instance().closeResource(conn, ps, rs); // 释放资源
            return flag;
        }
    }


    /**
     * 查询用户需要的cookie列表
     *
     * @return
     */
    @SuppressWarnings("finally")
    public static boolean prGetCookieList(String msg_id, UserInfo userInfo,
                                          List<UserCookie> cookieList) {
        boolean flag = false;

        String sql = "";
        Connection conn = null;
        NamedParameterStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.Instance().getConnection(msg_id, DB_NAME, logger); // 连接池获取链接
            if (null == conn) {
                throw new Exception(msg_id + "-获取数据库连接异常,:DBPoolName" + DB_NAME);
            }
            conn.setAutoCommit(false);

            //修改cookile为中间状态
            sql = " UPDATE tb_cookie" +
                    " set userId = 9" +
                    " where type = :type " +
                    " and userId = 0 " +
                    " order by lastRunTime asc " +
                    " limit :cookieCount ";
            ps = new NamedParameterStatement(conn, sql);
            ps.setString("type", userInfo.pingtaiType);
            ps.setInt("cookieCount", userInfo.cookieCount);
            ps.executeUpdate();

            //判断sessionId是否有效，并获取userId
            sql = " select " +
                    " id," +
                    " type," +
                    " cookie," +
                    " userName," +
                    " pwd," +
                    " creatTime," +
                    " lastRunTime," +
                    " userId" +
                    " from tb_cookie " +
                    " where userId = 9 ";
            ps = new NamedParameterStatement(conn, sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                UserCookie cookie = new UserCookie();
                cookie.id = rs.getString("id");
                cookie.type = rs.getString("type");
                cookie.cookie = rs.getString("cookie").trim().replaceAll(" ", "");
                cookie.userName = rs.getString("userName");
                cookie.pwd = rs.getString("pwd");
                cookie.creatTime = rs.getString("creatTime");
                cookie.lastRunTime = rs.getString("lastRunTime");
                cookie.userId = rs.getString("userId");
                cookieList.add(cookie);
            }

            //修改cookile为已被占用
            sql = " UPDATE tb_cookie" +
                    " set userId = :userId" +
                    " where userId = 9";
            ps = new NamedParameterStatement(conn, sql);
            ps.setString("userId", userInfo.id);
            ps.executeUpdate();

            flag = true;
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            logger.error(msg_id + ":" + DB_NAME + " - " + sql + "执行异常:"
                    + e);
        } finally {
            DBHelper.Instance().closeResource(conn, ps, rs); // 释放资源
            return flag;
        }
    }

    /**
     * 查询用户信息
     *
     * @return
     */
    @SuppressWarnings("finally")
    public static boolean prGetUserInfo(String msg_id, String machineCode,
                                        String regCode, UserInfo userInfo) {
        boolean flag = false;

        String sql = "";
        Connection conn = null;
        NamedParameterStatement ps = null;
        ResultSet rs = null;
        try {
            do {
                conn = DBHelper.Instance().getConnection(msg_id, DB_NAME, logger); // 连接池获取链接
                if (null == conn) {
                    throw new Exception(msg_id + "-获取数据库连接异常,:DBPoolName" + DB_NAME);
                }
                conn.setAutoCommit(false);

                //判断sessionId是否有效，并获取userId
                sql = " select count(1) count," +
                        " max(id) id, " +
                        " max(machineCode) machineCode, " +
                        " max(regCode) regCode, " +
                        " max(creatTime) creatTime, " +
                        " max(expTime) expTime, " +
                        " max(cookieCount) cookieCount, " +
                        " max(pingtaiType) pingtaiType " +
                        " from tb_user_info " +
                        " where regCode = :regCode";
                ps = new NamedParameterStatement(conn, sql);
                ps.setString("regCode", regCode);
                rs = ps.executeQuery();
                rs.next();
                userInfo.count = rs.getInt("count");
                if (1 > userInfo.count) {
                    logger.debug(msg_id + " - regCode信息不存在");
                    flag = true;
                    break;
                }

                userInfo.id = rs.getString("id");
                userInfo.regCode = regCode;
                userInfo.creatTime = rs.getString("creatTime");
                userInfo.expTime = rs.getString("expTime");
                userInfo.cookieCount = rs.getInt("cookieCount");
                userInfo.pingtaiType = rs.getString("pingtaiType");
                flag = true;

                String machineCodeDB = rs.getString("machineCode");
                if (StringUtil.isBlank(machineCodeDB)) {
                    //插入机器码
                    sql = "UPDATE tb_user_info " +
                            " set machineCode = :machineCode" +
                            " where regCode = :regCode ";
                    ps = new NamedParameterStatement(conn, sql);
                    ps.setString("machineCode", machineCode);
                    ps.setString("regCode", regCode);
                    int insertCount = ps.executeUpdate();
                    if (1 > insertCount) {
                        logger.debug(msg_id + " - 添加机器码用户[" + userInfo.id + "-" + regCode + "]失败！");
                        flag = false;
                    } else {
                        flag = true;
                        conn.commit();
                    }
                }

                if (!flag) {
                    logger.info(msg_id + " - 查询用户[" + userInfo.id + "-" + regCode + "]信息失败");
                } else {
                    logger.info(msg_id + " - 查询用户[" + userInfo.id + "-" + regCode + "]信息成功");
                }
            } while (false);
        } catch (Exception e) {
            conn.rollback();
            logger.error(msg_id + ":" + DB_NAME + " - " + sql + "执行异常:"
                    + e);
        } finally {
            DBHelper.Instance().closeResource(conn, ps, rs); // 释放资源
            return flag;
        }
    }
}
